#-*- coding:UTF-8 -*-


import unittest
import datetime
import time
import traceback

import MySQLdb

#from sqlalchemy.ext.declarative import declarative_base

from ..orm.db import *
from ..orm.db_util import *
from ..orm.models import *
from ..logger import logger
from ..ldap import get_LDAP_user_list

#Base=declarative_base()


def _object_query_by_id():
    logger.debug('test query object by id')
    user = get_object_by_id(User, 3)
    logger.debug('id(3) -> %s'%user)
    user = get_object_by_id(User, 333)
    logger.debug('id(333) -> %s'%user)


def _init_permission_data():
    logger.debug('init permission mapping data')
    with session_context() as session:
        cur_time = datetime.datetime.now()
        logger.debug('session = %s'%session)
        user1 = get_objects(User, name='lizhi9')[0]
        user2 = get_objects(User, name='lizx9')[0]
        user3 = get_objects(User, name='liyz2')[0]
        user4 = get_objects(User, name='zhangwu')[0]
        sys1 = get_objects(System, name='EBG_QAS')[0]
        sys2 = get_objects(System, name='TBG_PRE')[0]
        sys3 = get_objects(System, name='MBG_TST')[0]
        sys4 = get_objects(System, name='ELOIS_PRE')[0]
        us_map = {user1:(sys1, sys3, sys4), user2:(sys2, sys4), user3:(sys3, sys1, sys2), user4:(sys4,)}
        #actor_type = '%s.%s'%(User.__module__, User.__name__)
        #res_type = '%s.%s'%(System.__module__, System.__name__)
        actor_type = User.__name__
        res_type = System.__name__
        for u, l in us_map.items():
            logger.debug('u = %s, l=%s'%(u, l))
            for s in l:
                cnt = session.query(PermissionMapping).filter_by(actor_id=u.id).filter_by(actor_type=actor_type)\
                        .filter_by(res_type=res_type).filter_by(res_id=s.id).count()
                if cnt == 0:
                    logger.debug('add PermissionMapping(%s, %s)'%(u.name, s.name))
                    session.add(PermissionMapping(createtime=cur_time, updatetime=cur_time, actor_id=u.id, actor_type=actor_type, res_type=res_type, res_id=s.id))
                else:
                    logger.debug('existed PermissionMapping(%s, %s)'%(u.name, s.name))
        session.commit()
        logger.debug('end of permission data init')


def _query_permission_test():
    logger.debug('test query permissionmapping')
    with session_context() as session:
        logger.debug('session = %s'%session)
        user = session.query(User).filter_by(name='lizhi9').one()
        logger.debug('user = %s'%user)


def _object_query_test():
    user_list = get_objects(User, attr_list=['usergroups.group'])
    for user in user_list:
        logger.debug('user = %s'%user)
        for ug in user.usergroups:
            logger.debug('  usergroup = %s'%ug)
            logger.debug('    group = %s'%ug.group)
    system_list = get_objects(System, attr_list=['wcapp', 'hosts.auths'])
    for system in system_list:
        logger.debug('system = %s'%system)
        logger.debug('  wcapp = %s'%system.wcapp)
        for host in system.hosts:
            logger.debug('  host = %s'%host)
            for auth in host.auths:
                logger.debug('    auth = %s'%auth)
    _query_permission_test()
    logger.debug('test count function')
    user_count = count_object(User)
    group_count = count_object(Group)
    sys_count = count_object(System)
    host_count = count_object(Host)
    logger.debug('user_count  = %s '%user_count)
    logger.debug('group_count = %s '%group_count)
    logger.debug('sys_count   = %s '%sys_count)
    logger.debug('host_count  = %s '%host_count)


def _insert_db(cursor, table_name, column_list, value_list):
    assert len(column_list) == len(value_list), 'len(column_list) != len(value_list)'
    col_names = ','.join(column_list)
    val_names = ','.join(['%s' for i in value_list])
    sql = 'insert into %s(%s) values(%s)'%(table_name, col_names, val_names)
    #logger.debug('sql = %s'%sql)
    return cursor.execute(sql, value_list)


def _update_db(cursor, table_name, column_list, value_list, cnd_name_list, cnd_value_list):
    assert len(column_list) > 0 , 'len(column_list) == 0'
    assert len(column_list) == len(value_list), 'len(column_list) != len(value_list)'
    assert len(cnd_name_list) == len(cnd_value_list), 'len(cnd_name_list) != len(cnd_value_list)'
    col_names = ','.join(column_list)
    val_names = ','.join(['%s' for i in value_list])
    sub_sqls1 = ['%s=%%s'%col for col in column_list]
    sub_sqls2 = ['%s=%%s'%cnd for cnd in cnd_name_list]
    sql = 'update %s set %s where %s'%(table_name, ','.join(sub_sqls1), ' and '.join(sub_sqls2))
    #logger.debug('sql = %s'%sql)
    return cursor.execute(sql, value_list + cnd_value_list)


def _init_system_host_auth_data_from_201_86():
    logger.debug('init System/Host/Auth data from 10.99.201.86 mysql(cmdb)')
    cur_time = datetime.datetime.now()
    conn_src, conn_tgt = None, None
    try:
        conn_src = MySQLdb.connect(host='10.99.201.86',  user='cmdb',    passwd='cmdb123',    db='cmdb',    port=3306)
        conn_tgt = MySQLdb.connect(host='10.99.244.228', user='platmgt', passwd='platmgt123', db='platmgt', port=3306)
        cur_src = conn_src.cursor()
        cur_tgt = conn_tgt.cursor()
        count_tgt = cur_tgt.execute('select * from user where name = \'zhaoxp2\'')
        assert count_tgt > 0, 'user(zhaoxp) are not added into DB'
        creator_id = cur_tgt.fetchall()[0][0]
        logger.debug('creator\'s ID = %s'%creator_id)
        count_src = cur_src.execute('select * from sysid')
        logger.debug('Count of SysID = %s'%count_src)
        sysid_list = cur_src.fetchall()
        for sysid in sysid_list:
            #table System | WCApp
            count_tgt = cur_tgt.execute('select * from system where name=%s', (sysid[1]))
            system_id = 0
            if count_tgt == 0:
                #not found and insert one to 'system'
                logger.debug('  system(%s) not found and insert one into system | wcapp'%sysid[1])
                v_systype = 'TEST'
                if str(sysid[-1]) == '1':
                    v_systype = 'PRD'
                _insert_db(cur_tgt, 'system', ('name', 'createtime', 'updatetime', 'enabled', 'systype', 'creator_id'), (sysid[1], cur_time, cur_time, 1, v_systype, creator_id))
                cur_tgt.execute('select * from system where name=%s', (sysid[1]))
                system_id = cur_tgt.fetchall()[0][0]
                _insert_db(cur_tgt, 'wcapp', ('name', 'appadmin', 'apppass', 'url', 'createtime', 'updatetime', 'slavenumber', 'wthome', 'localwthome', 'localbackup', 'remotebackup', 'envset', 'startcmd', 'stopcmd', 'system_id'), \
                    (sysid[1], sysid[2], sysid[3], sysid[4], cur_time, cur_time, sysid[7], sysid[8], sysid[9], sysid[10], sysid[11], sysid[12], sysid[13], sysid[14], system_id))
            elif count_tgt == 1:
                system_id = cur_tgt.fetchall()[0][0]
                #update in taraget System
                v_systype = 'TEST'
                if str(sysid[-1]) == '1':
                    v_systype = 'PRD'
                _update_db(cur_tgt, 'system', ('updatetime', 'systype'), (cur_time, v_systype) ,('name',), (sysid[1],))
                #update WCApp
                cnt_tgt = cur_tgt.execute('select * from wcapp where system_id=%s',(system_id))
                if cnt_tgt == 0 :
                    #insert into wcapp
                    logger.debug('  system(%s) found but need to insert into wcapp'%sysid[1])
                    _insert_db(cur_tgt, 'wcapp', ('name', 'appadmin', 'apppass', 'url', 'createtime', 'updatetime', 'slavenumber', 'wthome', 'localwthome', 'localbackup', 'remotebackup', 'envset', 'startcmd', 'stopcmd', 'system_id'), \
                        (sysid[1], sysid[2], sysid[3], sysid[4], cur_time, cur_time, sysid[7], sysid[8], sysid[9], sysid[10], sysid[11], sysid[12], sysid[13], sysid[14], system_id))
                elif cnt_tgt == 1:
                    #update
                    logger.debug('  system(%s) found but need to update wcapp'%sysid[1])
                    _update_db(cur_tgt, 'wcapp', ('name', 'appadmin', 'apppass', 'url', 'updatetime', 'slavenumber', 'wthome', 'localwthome', 'localbackup', 'remotebackup', 'envset', 'startcmd', 'stopcmd'), \
                        (sysid[1], sysid[2], sysid[3], sysid[4], cur_time, sysid[7], sysid[8], sysid[9], sysid[10], sysid[11], sysid[12], sysid[13], sysid[14]), ('system_id',), (system_id,))
                    
                else:
                    logger.debug('  Too many WCApps : system = %s'%sysid[1])
            else:
                logger.debug('  Too many Systems : system = %s'%sysid[1])
            #table systemhostlink | host
            if system_id:
                logger.debug('-'*50)
                count_src = cur_src.execute('select * from host where sysid_id=%s', (sysid[0],))
                host_list = cur_src.fetchall()
                for host in host_list:
                    #search host
                    host_id = 0
                    cnt_tgt = cur_tgt.execute('select * from host where ip=%s',(host[1]))
                    if cnt_tgt == 0:
                        logger.debug('    host(%s) not found and insert one into host'%host[1])
                        _insert_db(cur_tgt, 'host', ('ip', 'name', 'servertype', 'createtime', 'updatetime', 'ostype', 'enabled', 'creator_id'), (host[1], host[2], host[4], cur_time, cur_time, host[7], 1, creator_id))
                        cur_tgt.execute('select * from host where ip=%s',(host[1]))
                        host_id = cur_tgt.fetchall()[0][0]
                    elif cnt_tgt == 1:
                        logger.debug('    host(%s) found and update auth'%host[1])
                        host_id = cur_tgt.fetchall()[0][0]
                        _update_db(cur_tgt, 'host', ('ip', 'name', 'servertype', 'updatetime', 'ostype'), (host[1], host[2], host[4], cur_time, host[7]), ('id',), (host_id,))
                    else:
                        logger.debug('Too many hosts: %s'%host[1])
                    if host_id:
                        #systemhostmapping
                        cnt_tgt = cur_tgt.execute('select * from systemhostlink where system_id=%s and host_id=%s',(system_id, host_id))
                        if cnt_tgt == 0:
                            logger.debug('    systemhostmaping(%s, %s) not found and insert one into systemhostlink'%(system_id, host_id))
                            _insert_db(cur_tgt, 'systemhostlink', ('system_id', 'host_id', 'createtime', 'updatetime'), (system_id, host_id, cur_time, cur_time))
                        elif cnt_tgt == 1:
                            _update_db(cur_tgt, 'systemhostlink', ('updatetime',), (cur_time,), ('system_id', 'host_id'), (system_id, host_id))
                        else:
                            logger.debug('Too many systemhostmapping: system_id(%s), host_id(%s)'%(system_id, host_id))
                        #table auth
                        count_src = cur_src.execute('select * from auth where host_id=%s', (host[0],))
                        auth_list = cur_src.fetchall()
                        for auth in auth_list:
                            auth_id = 0
                            cnt_tgt = cur_tgt.execute('select * from osauth where host_id=%s and username=%s',(host_id, auth[1]))
                            if cnt_tgt == 0:
                                logger.debug('      auth(%s) not found and insert one into auth'%auth[1])
                                _insert_db(cur_tgt, 'osauth', ('username', 'password', 'createtime', 'updatetime', 'can_sudo', 'host_id'), (auth[1], auth[2], cur_time, cur_time, auth[5], host_id))
                            elif cnt_tgt == 1:
                                logger.debug('      auth(%s) found and update auth'%auth[1])
                                auth_id = cur_tgt.fetchall()[0][0]
                                _update_db(cur_tgt, 'osauth', ('username', 'password', 'updatetime', 'can_sudo', 'host_id'), (auth[1], auth[2], cur_time, auth[5], host_id), ('id', 'username'), (auth_id, auth[1]))
                            else:
                                logger.debug('Too many auth: %s'%auth[1])
        conn_tgt.commit()
    except Exception as ex:
        logger.debug('Exception in _init_system_host_auth_data_from_201_86: %s'%ex)
        traceback.print_exc()
    finally:
        if conn_src:conn_src.close()
        if conn_tgt:conn_tgt.close()



def _init_DB_data_usergroup():
    with session_context() as session:
        cur_time=datetime.datetime.now()
        #for user
        user_list = get_LDAP_user_list()
        new_user_list = []
        commited = False
        admin_index = -1
        for index, user in enumerate(user_list):
            if user['uid'] == 'admin':
                admin_index = index
                query_result = session.query(User).filter_by(name=user['uid']).all()
                if not query_result:
                    admin_user = User(name=user['uid'], fullname=user['cn'], mail=user['mail'], createtime=cur_time, updatetime=cur_time)
                    session.add(admin_user)
                    new_user_list.append(admin_user)
                    commited = True
                else:
                    new_user_list.append(query_result[0])
                    logger.debug('User(%s) found in DB: %s'%(user['uid'], query_result[0]))
        if admin_index > -1:
            del user_list[admin_index]
        for user in user_list:
            query_result = session.query(User).filter_by(name=user['uid']).all()
            if not query_result:
                new_user = User(name=user['uid'], fullname=user['cn'], mail=user['mail'], createtime=cur_time, updatetime=cur_time)
                session.add(new_user)
                new_user_list.append(new_user)
                commited = True
            else:
                new_user_list.append(query_result[0])
                logger.debug('User(%s) found in DB: %s'%(user['uid'], query_result[0]))
        #for group
        group_list = []
        group_list.append(Group(name='Administrators', description='admin users with full permissions', createtime=cur_time, updatetime=cur_time))
        group_list.append(Group(name='TBG_Group', description='TBG users', createtime=cur_time, updatetime=cur_time))
        group_list.append(Group(name='MBG_Group', description='MBG users', createtime=cur_time, updatetime=cur_time))
        group_list.append(Group(name='EBG_Group', description='EBG users', createtime=cur_time, updatetime=cur_time))
        group_list.append(Group(name='ELOIS_Group', description='ELOIS users', createtime=cur_time, updatetime=cur_time))
        admin_group, tbg_group, mbg_group, ebg_group, elois_group = group_list
        new_group_list = []
        for group in group_list:
            query_result = session.query(Group).filter_by(name=group.name).all()
            if not query_result:
                session.add(group)
                new_group_list.append(group)
                commited = True
            else:
                new_group_list.append(query_result[0])
                logger.debug('Group(%s) found in DB: %s'%(group.name, query_result[0]))
        #for user-group mapping:
        ug_mapping = {
                    'admin': 'Administrators',\
                    'zhaoxp2': 'Administrators',\
                    'liuxt2': 'Administrators',\
                    'liyz2': 'TBG_Group',\
                    'lizx9': 'ELOIS_Group',\
                    'wende': 'EBG_Group',\
                    'zhangwu': 'TBG_Group',\
                    }
        print ug_mapping
        new_user_name_list = [u.name for u in new_group_list]
        new_group_name_list = [g.name for g in new_group_list]
        print new_user_name_list
        print new_group_name_list
        for user_name, group_name in ug_mapping.items():
            the_user, the_group = None, None
            for i in new_user_list:
                if user_name == i.name:
                    the_user = i
            for i in new_group_list:
                if group_name == i.name:
                    the_group = i
            if the_user is not None and the_group is not None:
                if the_user.id is not None and the_group.id is not None:
                    query_result = session.query(UserGroupMapping).filter_by(user_id=the_user.id, group_id=the_group.id).all()
                    if len(query_result) == 0:
                        session.add(UserGroupMapping(note='', user=the_user, group=the_group, createtime=cur_time, updatetime=cur_time))
                        commited = True
                    elif len(query_result) == 1:
                        logger.debug('UserGroupMapping existed: user_id = %s, group_id = %s'%(the_user.id, the_group.id))
                else:
                    session.add(UserGroupMapping(note='', user=the_user, group=the_group, createtime=cur_time, updatetime=cur_time))
                    commited = True
        if commited:
            session.commit()


def _init_DB_data():
    _init_DB_data_usergroup()
    _init_system_host_auth_data_from_201_86()
    _init_permission_data()


def _init_DB_schema():
    logger.debug('\nstart to init DB schema')
    logger.debug('DB engine = %s'%engine)
    Base.metadata.create_all(engine)
    logger.debug('end of initing DB schema')


def _drop_DB_schema():
    logger.debug('\nstart to drop DB schema')
    logger.debug('DB engine = %s'%engine)
    Base.metadata.drop_all(engine)
    logger.debug('end of droping DB schema')


class DBSchemaInit(unittest.TestCase):

    def test_DB_init(self):
        _init_DB_schema()


class DBSchemaDrop(unittest.TestCase):

    def test_DB_drop(self):
        _drop_DB_schema()


class DBDataInit(unittest.TestCase):

    def test_DB_data_init(self):
        _init_DB_data()


class DBQueryTest(unittest.TestCase):

    def test_DB_query(self):
        _object_query_test()
        print('')
        _object_query_by_id()